import sys,os
import pandas as pd
import re
import datetime,time
import random
import emoji
from pathlib import Path

''' 全文说明
输入文件是 评论列表、sku字典(运营人员长期维护的一个item列表xlsx文件)
评论列表字段依次：ID,Username,ProfileURL,Rate,RateText,Format,Title,Content,Helpful,Date,Thumbnails,Images,Video,Verified,ReviewURL
sku字典格式：
	sku_dic = {
		"WA|CN313T_AWB_2XL" : '525045817',
		"WA|CN313T_AWB_L" : '116097163',
		"WA|CN313T_AWB_M" : '474364828',
					...
	}
'''
# 全局变量 =================================
out_dir = 'data/output'  # 输出目录
# end

# 以指定概率生成某个数字，输入：参数1,[2,3,4,5] 参数2,[0.1,0.1,0.2,0.6]
def number_of_certain_probability(sequence, probability):
    x = random.uniform(0, 1)
    cumulative_probability = 0.0
    for item, item_probability in zip(sequence, probability):
        cumulative_probability += item_probability
        if x < cumulative_probability:
            break
    return item


# 从Review文件路径中获取spu代码和handle,路径格式:data/CN001D_lacozy-women-maxi-sleeveless-racerback-casual-dresses_B07TZNRFKK_789.xlsx
def getSpuCodeFromRviewfilePath(file_path):
	po = Path(file_path)
	name_str = po.name
	name_seg_list = name_str.split('_')
	return name_seg_list[0],name_seg_list[1]


# Date字符串解析，格式：Reviewed in the United States on December 17, 2021
def dateAndLoc(desc_str):
	pat = re.compile(r'Reviewed in[ a-zA-Z0-9]+on ([0-9a-zA-Z, ]+)')
	res = pat.match(desc_str)
	date_str = None
	if res!=None:
		date_str = res.group(1)
		date_str = datetime.datetime.strftime(datetime.datetime.strptime(date_str, "%B %d, %Y"), "%Y-%m-%d")
	return date_str


# 根据sku字典及Review行数，返回sku代码、Item ID的随机列表
def randomSkuAndID(sku_dic, row_count):
	skus = list(sku_dic.keys())
	maxIndex = len(skus)-1

	skucode_list = []
	itemid_list = []
	for i in range(row_count):
		index = random.randint(0, maxIndex)
		key = skus[index]
		#print(f"{skuDic[key]},{key}")
		itemid_list.append(sku_dic[key])
		skucode_list.append(key)
	return skucode_list, itemid_list

# 清洗review数据
def washData(csv_path):
	new_data = 'None'
	# 过滤emoji字符
	with open(csv_path, 'r', encoding='utf-8') as f:
		data = f.read()
		new_data = emoji.replace_emoji(data, '')
	# 替换Amazon字样
	new_data = new_data.replace('Amazon', 'Walmart')
	new_data = new_data.replace('amazon', 'walmart')
	with open(csv_path, 'w+') as f2:
		f2.write(new_data, encoding='utf-8')


def genReviewCSV(review_file_path, itemdic_file_path):
	spu_code,handle = getSpuCodeFromRviewfilePath(review_file_path)
	print(f'Path:{review_file_path}',spu_code,handle)

	# 先清除emoji字符
	washData(review_file_path)

	df_src = pd.read_excel(review_file_path, engine='xlrd')
	df_src = df_src.assign(Date=df_src['Date'].apply(dateAndLoc)) # 先对日期列数据转换格式
	df_src = df_src.assign(Rate=df_src['Rate'].apply(lambda x:number_of_certain_probability([2,3,4,5], [0.05,0.05,0.2,0.7])))   # 按指定概率生成Rate分数
	df_item_dict = pd.read_excel(itemdic_file_path)
	df_spu_item_dict = df_item_dict[df_item_dict['SPU']==spu_code]
	df_spu_item_dict = df_spu_item_dict[['sku','item id']]
	sku_dic = df_spu_item_dict.groupby('sku')['item id'].apply(lambda x:list(x)[0]).to_dict()
	
	row_count = df_src.shape[0]
	skulist,itemidlist = randomSkuAndID(sku_dic, row_count)

	# 先生成walmart申请表，字段依次：Walmart Item ID,SKU,Review Title,Review Body,Review Rating,Review Created Date,Review User Name
	df_walmart_apply = df_src[['Title','Content','Rate','Date','Username']].copy()
	#print(df_walmart_apply['Date'])
	df_walmart_apply['Walmart Item ID'] = itemidlist
	df_walmart_apply['SKU'] = skulist
	df_walmart_apply['URL link'] = f'https://lacozyco.com/products/{handle}'
	df_walmart_apply = df_walmart_apply[['Walmart Item ID','SKU','Title','Content','Rate','Date','Username','URL link']]  #对列重新排序
	df_walmart_apply.columns = ['Walmart Item ID','SKU','Review Title','Review Body','Review Rating','Review Created Date','Review User Name','URL link']
	# 输出
	df_walmart_apply.to_csv(f'{out_dir}/syndication_{spu_code}_{row_count}.csv', index=False)

	# 生成Ryviu导入模板，字段依次： product_handle,rating,title,author,email,body_text,body_urls,created_at,avatar,country_code,status,featured
	df_Ryviu_template = df_src[['Rate','Title','Username','Content','Date']].copy()
	df_Ryviu_template = df_Ryviu_template.assign(Date=df_Ryviu_template['Date'].apply(lambda x:datetime.datetime.strptime(x, "%Y-%m-%d").strftime("%Y-%m-%d %H:%M")))  # 日期格式转换成Ryviu规定的YYYY-MM-DD HH:mm
	# 添加必要列
	df_Ryviu_template['product_handle'] = handle.lower()
	df_Ryviu_template['email'] = 'admin@lacozyco.com'
	df_Ryviu_template['body_urls'] = ''
	df_Ryviu_template['avatar'] = ''
	df_Ryviu_template['country_code'] = 'US'   # 国家代码，以后可能会增加其他国家
	df_Ryviu_template['status'] = 'enable'
	df_Ryviu_template['featured'] = 0
	# 先对列重新排序，后重命名列名称
	df_Ryviu_template = df_Ryviu_template[['product_handle','Rate','Title','Username','email','Content','body_urls','Date','avatar','country_code','status','featured']] # 排序
	df_Ryviu_template.columns = ['product_handle','rating','title','author','email','body_text','body_urls','created_at','avatar','country_code','status','featured']  # 重命名
	# 输出
	df_Ryviu_template.to_csv(f'{out_dir}/Ryviu_{spu_code}_{row_count}.csv', index=False)

def main():
	if len(sys.argv)!=3:
		print("用法：python 脚本 目标文件夹路径 item字典文件路径")
		exit()
	target_dir = Path(sys.argv[1])
	itemdic_file = Path(sys.argv[2])
	if not target_dir.is_dir():
		print("目标文件夹路径参数不是文件夹")
		exit()
	if not itemdic_file.exists():
		print("item字典文件不存在")
		exit()
	flist = os.listdir(target_dir)
	for fn in flist:
		fo = Path(target_dir/fn)
		#print(fo.is_file())
		if fo.is_file():
			genReviewCSV(fo.absolute(), itemdic_file.absolute())

if __name__ == '__main__':
	main()